#!/usr/bin/python
# -*- coding=utf-8 -*-
import xlrd
import string
import pymysql
import cx_Oracle
import time
import datetime
import sys
import os
import configparser
reload(sys)
sys.setdefaultencoding("utf-8")
import os
os.environ["NLS_LANG"] = "SIMPLIFIED CHINESE_CHINA.UTF8"

def run(filepath):

    config = configparser.ConfigParser()
    config.read(filepath, encoding="utf-8")

    print time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) ,"获取配置文件信息"
    S_HOST = str(config.get("source","S_HOST"))
    S_UID = str(config.get("source","S_UID"))
    S_PWD = str(config.get("source","S_PWD"))
    S_DB = str(config.get("source","S_DB"))
    S_PORT = int(config.get("source","S_PORT"))
    S_CHARSET = str(config.get("source","S_CHARSET"))
    S_SQL = str(config.get("source","S_SQL"))
      
    T_HOST = str(config.get("target","T_HOST"))
    T_UID = str(config.get("target","T_UID"))
    T_PWD = str(config.get("target","T_PWD"))
    T_DB = str(config.get("target","T_DB"))
    T_PORT = int(config.get("target","T_PORT"))
    T_TBNAME = str(config.get("target","T_TBNAME"))
    T_PRESQL = str(config.get("target","T_PRESQL"))
    T_COMPLETESQL = str(config.get("target","T_COMPLETESQL"))
    T_COLS = str(config.get("target","T_COLS"))
    
    
    print time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) ,"初始化数据库信息"
    mydb = pymysql.connect(S_HOST,S_UID,S_PWD,S_DB,port=S_PORT,charset=S_CHARSET)
    mycursor = mydb.cursor()
    mycursor.execute("SET CHARACTER SET "+S_CHARSET+";")
    mycursor.execute("SET NAMES "+S_CHARSET+";")
    mycursor.execute("SET character_set_connection="+S_CHARSET+";")
    
    
    Ocon=cx_Oracle.connect(T_UID,T_PWD,T_HOST+":"+str(T_PORT)+"/"+T_DB)
 
    Ocursor = Ocon.cursor()
    
    step=int(config.get("common","C_CMTROWS")) 
    sqlrow_times = 0  
    
    
    #print mResult_tableDescription
    perCommit , p = int(config.get("common","C_PAGESIZE"))  , 0
    
    #KEY处理
    C_KEY_COL = str(config.get("common","C_KEY_COL"))
    C_KEY_COL_PAR = str(config.get("common","C_KEY_COL_PAR"))
    C_KEY_FILE = str(config.get("common","C_KEY_FILE"))
    C_KEY_MAX = "0"
    S_SQL = S_SQL.replace("`","")
    #print C_KEY_COL,C_KEY_COL_PAR,C_KEY_FILE
    if C_KEY_COL!="" and C_KEY_COL_PAR!="" and C_KEY_FILE!="":
        if(os.path.isfile(C_KEY_FILE)):
            #读取KEY文件，获取KEY值
            with open(C_KEY_FILE, 'r') as f:  # 打开文件
                lines = f.readlines()  # 读取所有行
                first_line = lines[0]  # 取第一行
                C_KEY_MAX=first_line
        else:
            C_KEY_MAX = "0"
        S_SQL = S_SQL.replace(C_KEY_COL_PAR,C_KEY_MAX)
        
        #获取本次最大值
        msql=" select max("+C_KEY_COL+") as MAXKEY from ("+ S_SQL + ") tbsearchtmp "
        #print msql
        mycursor.execute(msql)
        maxkey = mycursor.fetchone() 
        #mResult_tableDescription =  mycursor.description
        if maxkey[0] !=None:
            C_KEY_MAX = maxkey[0]

    
    tablename = T_TBNAME
    
    if T_PRESQL !="":
        print time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) ,"执行前置脚本"
        oSql = T_PRESQL
        Ocursor.prepare(oSql)
        Ocursor.execute(oSql)
        Ocon.commit()
    
    
    l=1
    print time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) ,"插入数据开始"   
    while 1 == 1 :
        print time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) ,"查询数据[",str((l-1)*step)," - " , str((l)*step),"]"
        msql=" select tbsearchtmp.* from ("+ S_SQL + ") tbsearchtmp limit  "+str((l-1)*step) + " , " + str(step)
        #print msql
        mycursor.execute(msql)
        mResult = mycursor.fetchall()
        #mResult_tableDescription =  mycursor.description
        
        #print "mResult",len(mResult)
        if len(mResult)<=0 :
            print time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) ,"[",str((l-1)*step)," - " , str((l)*step),"]已无数据，退出循环"
            break
            
        for row in mResult:
            p = p + 1
            oSql,C,Ci,colLen = "", "",0,len(row)
            for rrN in row:
                Ci = Ci + 1
                if "datetime.datetime" in str(type(rrN))  :
                    C = "to_date('" + str(rrN) + "', 'yyyy-mm-dd hh24:mi:ss')"
                elif "datetime.date" in str(type(rrN)):
                    C = "to_date('" + str(rrN) + "', 'yyyy-mm-dd')"
                elif rrN is None:
                    C = "NULL"
                else:
                    C = "'"+str(rrN).replace('"','').replace("'",'').replace(chr(9),'').replace(chr(10),'').replace(chr(13),'') +"'"
                if Ci == 1:
                    if T_COLS =="":
                        oSql = " insert into  " + tablename + " values( " + C
                    else:
                        oSql = " insert into  " + tablename + "("+T_COLS+") values( " + C
                else:
                    oSql = oSql + "," +  C
                if colLen == Ci:
                    oSql = oSql + ")"
            #print oSql
            Ocursor.prepare(oSql)
            rown = Ocursor.execute(oSql)
            #print p , p/500,p%500
            if p%perCommit == 0 :
                Ocon.commit()            
                print time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) , "已提交（",p, "）条"
        l=l+1    
            
        Ocon.commit()
        print time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) ,"已提交（",p, "）条"
                
       
        
    #执行后置脚本
    if T_COMPLETESQL !="":
        print time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) ,"执行后置脚本"
        oSql = T_COMPLETESQL
        Ocursor.prepare(oSql)
        Ocursor.execute(oSql)
        Ocon.commit()
    print time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) ,"执行完成：",filepath
    
    
    if C_KEY_COL!="" and C_KEY_COL_PAR!="" and C_KEY_FILE!="":
        with open(C_KEY_FILE, 'w') as f:  # 打开文件
            f.write(str(C_KEY_MAX))
            
    print time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) ,"关闭链接"
    Ocon.close        
    mycursor.close    
        
        
if __name__ == "__main__":
    print "================================================================================================"
    print "   _____                     .__    ___________      ________                      .__          "
    print "  /     \ ___.__. ___________|  |   \__    ___/___   \_____  \____________    ____ |  |   ____  "
    print " /  \ /  <   |  |/  ___/ ____/  |     |    | /  _ \   /   |   \_  __ \__  \ _/ ___\|  | _/ __ \ "
    print "/    Y    \___  |\___ < <_|  |  |__   |    |(  <_> ) /    |    \  | \// __ \\  \___|  |_\  ___/ "
    print "\____|__  / ____/____  >__   |____/   |____| \____/  \_______  /__|  (____  /\___  >____/\___  >"
    print "        \/\/         \/   |__|                               \/           \/     \/          \/ "
    print "================================================================================================"
    
    #调用事例：python mysql2oracle.py "/opt/pythonpkg/mysql2o/config/ZT_STORY.cfg"
    #注意：文件或文件夹路径为全路径
    try:
        #获取参数信息，参数为文件夹或配置文件，目前支持文件夹下所有配置按序执行，或单个文件执行，暂不支持*前缀匹配
        if len(sys.argv)!=2:
            print time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) ,"参数错误,格式应为：python mysql2oracle.py 配置文件路径"
            sys.exit(1)
        path = str(sys.argv[1])
        
        if os.path.isdir(path):
            #处理文件夹类型参数
            file_names = os.listdir(path)
            file_list = [os.path.join(path,file) for file in file_names]
            for file in file_list:
                if file.endswith(".cfg"):
                    print time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) ,"执行配置：",file
                    run(file)
        else:
            #处理文件类型参数
            if os.path.isfile(path):
                print time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) ,"执行配置：",path
                run(path)
            else:
                print time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) ,"参数错误：",path
                sys.exit(1) #异常返回
        sys.exit(0) #正常返回
    except Exception as r:
        print time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) ,"系统运行异常，错误原因",r
        sys.exit(1) #异常返回
    